package com.glowdayz.glowmee.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import org.apache.log4j.Logger;

import com.glowdayz.common.DBHandler;
import com.glowdayz.common.DateTime;
import com.glowdayz.common.PageHandler;
import com.glowdayz.common.MultipartBox.FileInfo;
import com.glowdayz.glowmee.cmd.BrandBannerCMD;
import com.glowdayz.glowmee.cmd.BrandCMD;

/** 브랜드 테이블 기능을 구현하는 클래스 */
public class BrandDAO {
	
	static Logger logger = Logger.getLogger(BrandDAO.class);
	
	private static BrandDAO instance = null;
	
	DBHandler db = null;
	
	public static BrandDAO getInstance() {
		if (instance == null) {
			instance = new BrandDAO();
		}
		return instance;
	}
	
	public BrandDAO() {
		db = DBHandler.getInstance();
	}
	
	/**
	 * 브랜드 전시여부수정
	 * @param cmd
	 * @return int
	 */
	public int unActivityBrand(BrandCMD cmd) throws SQLException {
		logger.debug("[unActivityBrand] ---------- Method Start ----------");
		logger.debug("[unActivityBrand][PARAM] BrandCMD cmd=" + cmd.toString());
		
		int result = 0;
		
		String sql = "UPDATE brand "
				+ "SET isDisplay=?, modified_date=? "
				+ "WHERE idBrand=?";
		PreparedStatement pstmt = db.getPrepareStatement(sql);
		
		try {
			pstmt.setInt(1, cmd.getIsDisplay());
			pstmt.setString(2, DateTime.getInstance().getNowDateTimeString());
			pstmt.setInt(3, cmd.getIdBrand());
			
			DBHandler.getInstance().beginTrans();
			result = pstmt.executeUpdate();
			
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			DBHandler.getInstance().rollback();
			logger.error("[unActivityBrand] " + e.getLocalizedMessage());
			logger.error("[unActivityBrand] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result > 0) {
			DBHandler.getInstance().commit();
		} else {
			DBHandler.getInstance().rollback();
		}
		
		logger.debug("[unActivityBrand][RETURN] result=" + result);
		logger.debug("[unActivityBrand] ---------- Method End ----------");
		
		return result;
	}
	
	/**
	 * 브랜드 추가
	 * @param cmd
	 * @return int
	 */
	public int insertBrand(BrandCMD cmd) throws Exception {
		logger.debug("[insertBrand] ---------- Method Start ----------");
		logger.debug("[insertBrand][PARAM] BrandCMD cmd=" + cmd.toString());
		
		int result = 0;
		
		PreparedStatement pstmt = null;
		
		String sql = "INSERT INTO brand("
				+ "brandTitle, sortKey, isDisplay, isRecommend, brandUrl, brandFacebook, twitter, kakaotalk, youTube, tel, create_date"
				+ ") VALUES (?, 1, 1, 0, ?, ?, ?, ?, ?, ?, ?)";
		
		pstmt = db.getPrepareStatement(sql);
		
		DBHandler.getInstance().beginTrans();
		try {
			pstmt.setString(1, cmd.getBrandTitle());
			pstmt.setString(2, cmd.getBrandUrl());
			pstmt.setString(3, cmd.getBrandFacebook());
			pstmt.setString(4, cmd.getTwitter());
			pstmt.setString(5, cmd.getKakaotalk());
			pstmt.setString(6, cmd.getYouTube());
			pstmt.setString(7, cmd.getTel());
			pstmt.setString(8, DateTime.getInstance().getNowDateTimeString());
			result = pstmt.executeUpdate();
			
			if (result > 0) {
				// 자동증가 일련변호 조회
				ResultSet rs = pstmt.getGeneratedKeys();
				if (rs.next()) {
					result = rs.getInt(1);
				}
				rs.close();
				rs = null;
			}
			
			pstmt.close();
			pstmt = null;
			
			if (cmd.getImage() != null) {
				
				String fileSql = "INSERT INTO attachefile ("
						+ "tableName, tableKey, fileOrgName, fileSaveName, fileDir, fileSize, fileType, isDisplay, create_date, isUser"
						+ ") VALUES ('brand', ?, ?, ?, ?, ?, ?, 1, ?, 0)";
				
				
				int fileResult = 0;
				
				FileInfo item = cmd.getImage();
				
				pstmt = db.getPrepareStatement(fileSql);
				pstmt.setInt(1, result);
				pstmt.setString(2, item.getFileOrgName());
				pstmt.setString(3, item.getFileSaveName());
				pstmt.setString(4, item.getFileDir());
				pstmt.setLong(5, item.getFileSize());
				pstmt.setString(6, item.getFileType());
				pstmt.setString(7, DateTime.getInstance().getNowDateTimeString());
				
				fileResult = pstmt.executeUpdate();
				
				if (fileResult > 0) {
					// 자동증가 일련변호 조회
					ResultSet rs = pstmt.getGeneratedKeys();
					if (rs.next()) {
						fileResult = rs.getInt(1);
					}
					rs.close();
					rs = null;
				} else {
					// 모든 데이터 저장 취소
					pstmt.close();
					pstmt = null;
					DBHandler.getInstance().rollback();
					return -1;
				}
				
				pstmt.close();
				pstmt = null;
					
				
				String update = "UPDATE brand "
						+ "SET brandImg=? "
						+ "WHERE idBrand=?";
				pstmt = db.getPrepareStatement(update);
				pstmt.setInt(1, fileResult);
				pstmt.setInt(2, result);
				result = pstmt.executeUpdate();
				pstmt.close();
				pstmt = null;
			}
		} catch (SQLException e) {
			DBHandler.getInstance().rollback();
			logger.error("[insertBrand] " + e.getLocalizedMessage());
			logger.error("[insertBrand] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result > 0) {
			DBHandler.getInstance().commit();
		} else {
			DBHandler.getInstance().rollback();
		}
		
		logger.debug("[insertBrand][RETURN] result=" + result);
		logger.debug("[insertBrand] ---------- Method End ----------");
		
		return result;
	}
	
	/**
	 * 브랜드 수정
	 * @param cmd
	 * @param delete_file
	 * @return int
	 * @throws SQLException 
	 */
	public int updateBrand(BrandCMD cmd) throws SQLException {
		logger.debug("[updateBrand] ---------- Method Start ----------");
		logger.debug("[updateBrand][PARAM] BrandCMD cmd=" + cmd.toString());
		
		int result = 0;
		
		DBHandler.getInstance().beginTrans();
		PreparedStatement pstmt = null;
		
		// (3) 게시물 정보 갱신
		String sql = "UPDATE brand "
				+ "SET brandTitle=?, isDisplay=?, brandUrl=?, brandFacebook=?, twitter=?, kakaotalk=?, youTube=?, tel=?, modified_date=? "
				+ "WHERE idBrand=?";
		pstmt = db.getPrepareStatement(sql);
		
		try {
			pstmt.setString(1, cmd.getBrandTitle());
			pstmt.setInt(2, cmd.getIsDisplay());
			pstmt.setString(3, cmd.getBrandUrl());
			pstmt.setString(4, cmd.getBrandFacebook());
			pstmt.setString(5, cmd.getTwitter());
			pstmt.setString(6, cmd.getKakaotalk());
			pstmt.setString(7, cmd.getYouTube());
			pstmt.setString(8, cmd.getTel());
			pstmt.setString(9, DateTime.getInstance().getNowDateTimeString());
			pstmt.setInt(10, cmd.getIdBrand());
			
			result = pstmt.executeUpdate();
			
			pstmt.close();
			pstmt = null;
			
			if (cmd.getImage() != null) {
				
				String fileSql = "INSERT INTO attachefile ("
						+ "tableName, tableKey, fileOrgName, fileSaveName, fileDir, fileSize, fileType, isDisplay, create_date, isUser"
						+ ") VALUES ('brand', ?, ?, ?, ?, ?, ?, 1, ?, 0)";
				
				int fileResult = 0;
				
				FileInfo item = cmd.getImage();
				
				pstmt = db.getPrepareStatement(fileSql);
				pstmt.setInt(1, cmd.getIdBrand());
				pstmt.setString(2, item.getFileOrgName());
				pstmt.setString(3, item.getFileSaveName());
				pstmt.setString(4, item.getFileDir());
				pstmt.setLong(5, item.getFileSize());
				pstmt.setString(6, item.getFileType());
				pstmt.setString(7, DateTime.getInstance().getNowDateTimeString());
				
				fileResult = pstmt.executeUpdate();
				
				if (fileResult > 0) {
					// 자동증가 일련변호 조회
					ResultSet rs = pstmt.getGeneratedKeys();
					if (rs.next()) {
						fileResult = rs.getInt(1);
					}
					rs.close();
					rs = null;
				} else {
					// 모든 데이터 저장 취소
					pstmt.close();
					pstmt = null;
					DBHandler.getInstance().rollback();
					return -1;
				}
				
				pstmt.close();
				pstmt = null;
				
				String update = "UPDATE brand "
						+ "SET brandImg=? "
						+ "WHERE idBrand=?";
				pstmt = db.getPrepareStatement(update);
				pstmt.setInt(1, fileResult);
				pstmt.setInt(2, cmd.getIdBrand());
				result = pstmt.executeUpdate();
				pstmt.close();
				pstmt = null;
			}
			
		} catch (SQLException e) {
			DBHandler.getInstance().rollback();
			logger.error("[updateBrand] " + e.getLocalizedMessage());
			logger.error("[updateBrand] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result > 0) {
			DBHandler.getInstance().commit();
		} else {
			DBHandler.getInstance().rollback();
		}
		
		logger.debug("[updateBrand][RETURN] result=" + result);
		logger.debug("[updateBrand] ---------- Method End ----------");
		
		return result;
	}
	
	/**
	 * 브랜드 조회
	 * @param idBrand
	 * @return BrandCMD
	 */
	public BrandCMD getBrand(int idBrand) throws SQLException {
		logger.debug("[getBrand] ---------- Method Start ----------");
		logger.debug("[getBrand][PARAM] idBrand=" + idBrand);
		
		BrandCMD result = null;
		String sql = "SELECT b.idBrand, brandTitle, b.isDisplay, brandUrl, brandFacebook, twitter, kakaotalk, youTube, b.tel, b.brandImg, b.create_date, count(p.idProduct) "
				+ "FROM brand b LEFT OUTER JOIN product p ON b.idBrand = p.idBrand "
				+ "WHERE b.idBrand=?";
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		
		try {
			pstmt.setInt(1, idBrand);
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				if (rs.next()) {
					int num = rs.getInt(1);
					String brandTitle = rs.getString(2);
					int isDisplay = rs.getInt(3);
					String brandUrl = rs.getString(4);
					String brandFacebook =rs.getString(5);
					String twitter = rs.getString(6);
					String kakaotalk = rs.getString(7);
					String youTube = rs.getString(8);
					String tel = rs.getString(9);
					int brandImg = rs.getInt(10);
					String create_date = rs.getString(11);
					int count = rs.getInt(12);
					
					FileInfo image = this.getImage(num);
					
					result = new BrandCMD(num, brandTitle, isDisplay, brandUrl, brandFacebook, twitter, kakaotalk, youTube, tel, 
							brandImg, image, create_date, count);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error("[getBrand] " + e.getLocalizedMessage());
			logger.error("[getBrand] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result != null) {
			logger.debug("[getBrand][RETURN] result=" + result.toString());
		}
		logger.debug("[getBrand] ---------- Method End ----------");
		
		return result;
	}
	
	public BrandCMD getBrandXml(int idBrand) throws SQLException {
		logger.debug("[getBrand] ---------- Method Start ----------");
		logger.debug("[getBrand][PARAM] idBrand=" + idBrand);
		
		BrandCMD result = null;
		String sql = "SELECT idBrand, brandTitle, brandUrl, brandFacebook, twitter, kakaotalk, youTube, brandImg, "
				+ "(SELECT COUNT(*) FROM pouch WHERE pouch.isDisplay=1 AND pouch.idBrand=brand.idBrand), "
				+ "(SELECT COUNT(*) FROM admintable WHERE admintable.isDisplay=1 AND admintable.idBrand=brand.idBrand) "
				+ "FROM brand "
				+ "WHERE isDisplay=1 AND idBrand=?";
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		
		try {
			pstmt.setInt(1, idBrand);
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				if (rs.next()) {
					int num = rs.getInt(1);
					String brandTitle = rs.getString(2);
					String brandUrl = rs.getString(3);
					String brandFacebook =rs.getString(4);
					String twitter = rs.getString(5);
					String kakaotalk = rs.getString(6);
					String youTube = rs.getString(7);
					int brandImg = rs.getInt(8);
					int pouchCount = rs.getInt(9);
					int eventCount = rs.getInt(10);
					
					FileInfo image = this.getImage(num);
					ArrayList<BrandBannerCMD> banner_list = this.getBrandBanner(num);
					
					result = new BrandCMD(idBrand, brandTitle, brandUrl, brandFacebook, twitter, kakaotalk, youTube, brandImg, pouchCount, eventCount, image, banner_list);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error("[getBrand] " + e.getLocalizedMessage());
			logger.error("[getBrand] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result != null) {
			logger.debug("[getBrand][RETURN] result=" + result.toString());
		}
		logger.debug("[getBrand] ---------- Method End ----------");
		
		return result;
	}
	
	/**
	 * 브랜드 리스트 조회
	 * @param page
	 * @param count
	 * @param order
	 * @param where
	 * @return ArrayList<BrandCMD>
	 */
	public ArrayList<BrandCMD> getBrandList(int page, int count, String order, String where) throws Exception {
		logger.debug("[getBrandList] ---------- Method Start ----------");
		logger.debug("[getBrandList][PARAM] page=" + page);
		logger.debug("[getBrandList][PARAM] count=" + count);
		logger.debug("[getBrandList][PARAM] order=" + order);
		logger.debug("[getBrandList][PARAM] where=" + where);
		
		ArrayList<BrandCMD> result = null;
		int cnt;
		if (where == null) {
			cnt = db.getCount("brand", "idBrand", null);
		} else {
			cnt = db.getCount("brand b", "b.idBrand", where);
		}
		
		if (count == -1) {
			count = cnt;
		}
		
		String sql = null;
		if (order == null && where == null) {
			sql = "SELECT b.idBrand, brandTitle, b.isDisplay, brandUrl, brandFacebook, twitter, kakaotalk, youTube, b.tel, b.brandImg, "
					+ "b.create_date, count(p.idProduct) "
					+ "FROM brand b LEFT OUTER JOIN product p ON b.idBrand = p.idBrand "
					+ "GROUP BY b.idBrand ORDER BY b.idBrand DESC "
					+ "LIMIT ?, ?";
		} else if (order != null && where != null) {
			sql = "SELECT b.idBrand, brandTitle, b.isDisplay, brandUrl, brandFacebook, twitter, kakaotalk, youTube, b.tel, b.brandImg, "
					+ "b.create_date, count(p.idProduct) "
					+ "FROM brand b LEFT OUTER JOIN product p ON b.idBrand = p.idBrand WHERE " + where + " "
					+ "GROUP BY b.idBrand ORDER BY " + order + " "
					+ "LIMIT ?, ?";
		} else if (order != null) {
			sql = "SELECT b.idBrand, brandTitle, b.isDisplay, brandUrl, brandFacebook, twitter, kakaotalk, youTube, b.tel, b.brandImg, "
					+ "b.create_date, count(p.idProduct) "
					+ "FROM brand b LEFT OUTER JOIN product p ON b.idBrand = p.idBrand "
					+ "GROUP BY b.idBrand ORDER BY " + order + " "
					+ "LIMIT ?, ?";
		} else if (where != null) {
			sql = "SELECT b.idBrand, brandTitle, b.isDisplay, brandUrl, brandFacebook, twitter, kakaotalk, youTube, b.tel, b.brandImg, "
					+ "b.create_date, count(p.idProduct) "
					+ "FROM brand b LEFT OUTER JOIN product p ON b.idBrand = p.idBrand WHERE " + where + " "
					+ "GROUP BY b.idBrand ORDER BY b.idBrand DESC "
					+ "LIMIT ?, ?";
		}
		
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		
		if (cnt == 0) {
			PageHandler.getInstance(page, cnt);
		} else {
			// 페이징 처리 --> 한 페이지에 count개씩, 페이지번호는 10개 출력
			PageHandler.getInstance(page, cnt, count, 10);
		}
		
		try {
			pstmt.setInt(1, PageHandler.getInstance().getStartRec());
			pstmt.setInt(2, PageHandler.getInstance().getPageArticleCount());
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				
				// SQL문장에 이상이 없을 경우에만 list 객체를 할당한다.
				// 이 객체가 null이라면 SQL실행에 실패한 경우이다.
				// 객체가 생성되지만, add()가 실행되지 않아서, size()값이 0이라면
				// 조건에 맞는 데이터가 없는 경우이다.
				result = new ArrayList<BrandCMD>();
				
				while(rs.next()) {
					int idBrand = rs.getInt(1);
					String brandTitle = rs.getString(2);
					int isDisplay = rs.getInt(3);
					String brandUrl = rs.getString(4);
					String brandFacebook = rs.getString(5);
					String twitter = rs.getString(6);
					String kakaotalk = rs.getString(7);
					String youTube = rs.getString(8);
					String tel = rs.getString(9);
					int brandImg = rs.getInt(10);
					String create_date = rs.getString(11);
					int productCount = rs.getInt(12);
					BrandCMD item = new BrandCMD(idBrand, brandTitle, isDisplay, brandUrl, brandFacebook, twitter, kakaotalk, youTube, tel, brandImg, 
							create_date, productCount);
					result.add(item);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error("[getBrandList] " + e.getLocalizedMessage());
			logger.error("[getBrandList] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result != null) {
			logger.debug("[getBrandList][RETURN] result_count=" + result.size());
		}
		logger.debug("[getBrandList] ---------- Method End ----------");
		
		return result;
	}
	
	/** 브랜드 리스트 xml */
	public ArrayList<BrandCMD> getBrandList(int page, int count, String where) throws Exception {
		logger.debug("[getBrandList] ---------- Method Start ----------");
		logger.debug("[getBrandList][PARAM] page=" + page);
		logger.debug("[getBrandList][PARAM] count=" + count);
		logger.debug("[getBrandList][PARAM] where=" + where);
		
		ArrayList<BrandCMD> result = null;
		int cnt;
		if (where == null) {
			cnt = db.getCount("brand", "idBrand", "isDisplay=1");
		} else {
			cnt = db.getCount("brand", "idBrand", "isDisplay=1 AND " + where);
		}
		
		if (count == -1) {
			count = cnt;
		}
		
		String sql = null;
		if (where == null) {
			sql = "SELECT b.idBrand, b.brandTitle, "
					+ "(SELECT COUNT(*) FROM product p WHERE p.isDisplay=1 AND b.idBrand=p.idBrand) "
					+ "FROM brand b "
					+ "WHERE b.isDisplay=1 "
					+ "ORDER BY (CASE WHEN ASCII(SUBSTRING(b.brandTitle,1)) BETWEEN 48 AND 57 THEN 3 "
					+ "WHEN ASCII(SUBSTRING(b.brandTitle,1)) < 128 THEN 2 ELSE 1 END), b.brandTitle ASC "
					+ "LIMIT ?, ?";
		} else if (where != null) {
			sql = "SELECT b.idBrand, b.brandTitle, "
					+ "(SELECT COUNT(*) FROM product p WHERE p.isDisplay=1 AND b.idBrand=p.idBrand) "
					+ "FROM brand b "
					+ "WHERE b.isDisplay=1 AND " + where + " "
					+ "ORDER BY (CASE WHEN ASCII(SUBSTRING(b.brandTitle,1)) BETWEEN 48 AND 57 THEN 3 "
					+ "WHEN ASCII(SUBSTRING(b.brandTitle,1)) < 128 THEN 2 ELSE 1 END), b.brandTitle ASC "
					+ "LIMIT ?, ?";
		}
		
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		
		if (cnt == 0) {
			PageHandler.getInstance(page, cnt);
		} else {
			// 페이징 처리 --> 한 페이지에 count개씩, 페이지번호는 10개 출력
			PageHandler.getInstance(page, cnt, count, 10);
		}
		
		try {
			pstmt.setInt(1, PageHandler.getInstance().getStartRec());
			pstmt.setInt(2, PageHandler.getInstance().getPageArticleCount());
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				
				// SQL문장에 이상이 없을 경우에만 list 객체를 할당한다.
				// 이 객체가 null이라면 SQL실행에 실패한 경우이다.
				// 객체가 생성되지만, add()가 실행되지 않아서, size()값이 0이라면
				// 조건에 맞는 데이터가 없는 경우이다.
				result = new ArrayList<BrandCMD>();
				
				while(rs.next()) {
					int idBrand = rs.getInt(1);
					String brandTitle = rs.getString(2);
					int productCount = rs.getInt(3);
					FileInfo image = this.getImage(idBrand);
					
					BrandCMD item = new BrandCMD(idBrand, brandTitle, productCount, image);
					result.add(item);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error("[getBrandList] " + e.getLocalizedMessage());
			logger.error("[getBrandList] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result != null) {
			logger.debug("[getBrandList][RETURN] result_count=" + result.size());
		}
		logger.debug("[getBrandList] ---------- Method End ----------");
		
		return result;
	}
	
	/**
	 * 추천브랜드 등록
	 * @param cmd
	 * @return int
	 * @throws SQLException 
	 */
	public int insertRecommendBrand(BrandCMD cmd) throws SQLException {
		logger.debug("[insertRecommendBrand] ---------- Method Start ----------");
		logger.debug("[insertRecommendBrand][PARAM] BrandCMD cmd=" + cmd.toString());
		
		int result = 0;
		
		String sql = "UPDATE brand "
				+ "SET promotionText=?, sortKey=?, isRecommend=1, start_date=?, end_date=?, modified_date=? "
				+ "WHERE idBrand=?";
		PreparedStatement pstmt = db.getPrepareStatement(sql);
		
		try {
			pstmt.setString(1, cmd.getPromotionText());
			pstmt.setInt(2, cmd.getSortKey());
			pstmt.setString(3, cmd.getStart_date());
			pstmt.setString(4, cmd.getEnd_date());
			pstmt.setString(5, DateTime.getInstance().getNowDateTimeString());
			pstmt.setInt(6, cmd.getIdBrand());
			
			DBHandler.getInstance().beginTrans();
			result = pstmt.executeUpdate();
			
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			DBHandler.getInstance().rollback();
			logger.error("[insertRecommendBrand] " + e.getLocalizedMessage());
			logger.error("[insertRecommendBrand] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result > 0) {
			DBHandler.getInstance().commit();
		} else {
			DBHandler.getInstance().rollback();
		}
		
		logger.debug("[insertRecommendBrand][RETURN] result=" + result);
		logger.debug("[insertRecommendBrand] ---------- Method End ----------");
		
		return result;
	}
	
	/**
	 * 추천브랜드 수정
	 * @param cmd
	 * @return int
	 * @throws SQLException 
	 */
	public int updateRecommendBrand(BrandCMD cmd) throws SQLException {
		logger.debug("[updateRecommendBrand] ---------- Method Start ----------");
		logger.debug("[updateRecommendBrand][PARAM] BrandCMD cmd=" + cmd.toString());
		
		int result = 0;
		
		String sql = "UPDATE brand "
				+ "SET promotionText=?, sortKey=?, start_date=?, end_date=?, modified_date=? "
				+ "WHERE idBrand=?";
		PreparedStatement pstmt = db.getPrepareStatement(sql);
		
		try {
			pstmt.setString(1, cmd.getPromotionText());
			pstmt.setInt(2, cmd.getSortKey());
			pstmt.setString(3, cmd.getStart_date());
			pstmt.setString(4, cmd.getEnd_date());
			pstmt.setString(5, DateTime.getInstance().getNowDateTimeString());
			pstmt.setInt(6, cmd.getIdBrand());
			
			DBHandler.getInstance().beginTrans();
			result = pstmt.executeUpdate();
			
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			DBHandler.getInstance().rollback();
			logger.error("[updateRecommendBrand] " + e.getLocalizedMessage());
			logger.error("[updateRecommendBrand] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result > 0) {
			DBHandler.getInstance().commit();
		} else {
			DBHandler.getInstance().rollback();
		}
		
		logger.debug("[updateRecommendBrand][RETURN] result=" + result);
		logger.debug("[updateRecommendBrand] ---------- Method End ----------");
		
		return result;
	}
	
	/**
	 * 추천브랜드 삭제
	 * @param delete_list
	 * @return int
	 * @throws SQLException 
	 */
	public int deleteRecommendBrand(String[] delete_list) throws SQLException {
		logger.debug("[deleteRecommendBrand] ---------- Method Start ----------");
		
		int result = 0;
		
		DBHandler.getInstance().beginTrans();
		
		if (delete_list.length > 0) {
			String in_cause = "(";
			for (int i=0; i<delete_list.length; i++) {
				in_cause += delete_list[i];
				if (i + 1 < delete_list.length) {
					in_cause += ",";
				}
			}
			in_cause += ")";
			
			// 게시물 데이터 삭제처리
			String delSql = "UPDATE brand "
					+ "SET isRecommend=0, promotionText=null, sortKey=1, start_date=null, end_date=null, modified_date=? "
					+ "WHERE isRecommend=1 AND idBrand in " + in_cause;
			PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(delSql);
			
			try {
				pstmt.setString(1, DateTime.getInstance().getNowDateTimeString());
				result = pstmt.executeUpdate();
				pstmt.close();
				pstmt = null;
			} catch (SQLException e) {
				DBHandler.getInstance().rollback();
				logger.error("[deleteRecommendBrand] " + e.getLocalizedMessage());
				logger.error("[deleteRecommendBrand] " + e.getSQLState());
			} finally {
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
			}
		}
		
		if (result > 0) {
			DBHandler.getInstance().commit();
		} else {
			DBHandler.getInstance().rollback();
		}
		
		logger.debug("[deleteRecommendBrand][RETURN] result=" + result);
		logger.debug("[deleteRecommendBrand] ---------- Method End ----------");
		
		return result;
	}
	
	/**
	 * 추천브랜드 리스트
	 * @param page
	 * @param count
	 * @return ArrayList<BrandCMD>
	 */
	public ArrayList<BrandCMD> getRecommendBrandList(int page, int count) throws Exception {
		logger.debug("[getRecommendBrandList] ---------- Method Start ----------");
		logger.debug("[getRecommendBrandList][PARAM] page=" + page);
		logger.debug("[getRecommendBrandList][PARAM] count=" + count);
		
		ArrayList<BrandCMD> result = null;
		int cnt = db.getCount("brand", "idBrand", "isRecommend=1");
		
		String sql = "SELECT idBrand, brandTitle, promotionText, sortKey, start_date, end_date "
					+ "FROM brand "
					+ "WHERE isRecommend=1 "
					+ "ORDER BY sortKey, modified_date DESC "
					+ "LIMIT ?, ?";
		
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		
		if (cnt == 0) {
			PageHandler.getInstance(page, cnt);
		} else {
			// 페이징 처리 --> 한 페이지에 count개씩, 페이지번호는 10개 출력
			PageHandler.getInstance(page, cnt, count, 10);
		}
		
		try {
			pstmt.setInt(1, PageHandler.getInstance().getStartRec());
			pstmt.setInt(2, PageHandler.getInstance().getPageArticleCount());
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				
				// SQL문장에 이상이 없을 경우에만 list 객체를 할당한다.
				// 이 객체가 null이라면 SQL실행에 실패한 경우이다.
				// 객체가 생성되지만, add()가 실행되지 않아서, size()값이 0이라면
				// 조건에 맞는 데이터가 없는 경우이다.
				result = new ArrayList<BrandCMD>();
				
				while(rs.next()) {
					int idBrand = rs.getInt(1);
					String brandTitle = rs.getString(2);
					String promotionText = rs.getString(3);
					int sortKey = rs.getInt(4);
					String start_date = rs.getString(5);
					String end_date = rs.getString(6);
					BrandCMD item = new BrandCMD(idBrand, brandTitle, promotionText, sortKey, start_date, end_date);
					result.add(item);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error("[getRecommendBrandList] " + e.getLocalizedMessage());
			logger.error("[getRecommendBrandList] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result != null) {
			logger.debug("[getRecommendBrandList][RETURN] result_count=" + result.size());
		}
		logger.debug("[getRecommendBrandList] ---------- Method End ----------");
		
		return result;
	}
	
	/** 추천브랜드 리스트 xml */
	public ArrayList<BrandCMD> getRecommendBrandList() throws Exception {
		logger.debug("[getRecommendBrandList] ---------- Method Start ----------");
		
		ArrayList<BrandCMD> result = null;
		
		int cnt = db.getCount("brand", "idBrand", "isRecommend=1");
		
		String date = DateTime.getInstance().getNowDateTimeString();
		String sql = "SELECT idBrand, brandTitle, promotionText, isRecommend "
				+ "FROM brand "
				+ "WHERE isRecommend=1 AND isDisplay=1 AND start_date<" + date + " AND end_date>" + date + " "
				+ "ORDER BY sortKey, brandTitle ASC";
		
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		PageHandler.getInstance(1, cnt);
		
		try {
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				
				// SQL문장에 이상이 없을 경우에만 list 객체를 할당한다.
				// 이 객체가 null이라면 SQL실행에 실패한 경우이다.
				// 객체가 생성되지만, add()가 실행되지 않아서, size()값이 0이라면
				// 조건에 맞는 데이터가 없는 경우이다.
				result = new ArrayList<BrandCMD>();
				
				while(rs.next()) {
					int idBrand = rs.getInt(1);
					String brandTitle = rs.getString(2);
					String promotionText = rs.getString(3);
					int isRecommend = rs.getInt(4);
					FileInfo image = this.getImage(idBrand);
					
					BrandCMD item = new BrandCMD(idBrand, brandTitle, promotionText, isRecommend, image);
					result.add(item);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error("[getRecommendBrandList] " + e.getLocalizedMessage());
			logger.error("[getRecommendBrandList] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result != null) {
			logger.debug("[getRecommendBrandList][RETURN] result_count=" + result.size());
		}
		logger.debug("[getRecommendBrandList] ---------- Method End ----------");
		
		return result;
	}
	
	/** 제품 이름 */
	public ArrayList<String> getBrandTitleList(String typehead) throws SQLException {
		logger.debug("[getBrandTitleList] ---------- Method Start ----------");
		
		ArrayList<String> result = null;
		String sql = "SELECT brandTitle FROM brand WHERE replace(brandTitle,' ','') like '%" + typehead + "%'";
		
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		
		try {
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				
				// SQL문장에 이상이 없을 경우에만 list 객체를 할당한다.
				// 이 객체가 null이라면 SQL실행에 실패한 경우이다.
				// 객체가 생성되지만, add()가 실행되지 않아서, size()값이 0이라면
				// 조건에 맞는 데이터가 없는 경우이다.
				result = new ArrayList<String>();
				
				while(rs.next()) {
					String productTitle = rs.getString(1);
					result.add(productTitle);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error("[getBrandTitleList] " + e.getLocalizedMessage());
			logger.error("[getBrandTitleList] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		logger.debug("[getBrandTitleList] ---------- Method End ----------");
		return result;
	}
	
	/**
	 * 이미지 리스트
	 * @param idx
	 * @return ArrayList<FileInfo>
	 */
	public ArrayList<FileInfo> getImageList(int idx) throws SQLException {
		String fileSql = "SELECT fileOrgName, idAttacheFile, fileSaveName, fileDir, fileSize, fileType "
				+ "FROM attachefile "
				+ "WHERE tableName='brand' AND tableKey=?";
		
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(fileSql);
		ArrayList<FileInfo> imageList = null;
		
		try {
			pstmt.setInt(1, idx);
			ResultSet rs = pstmt.executeQuery();
			if (rs != null) {
				imageList = new ArrayList<FileInfo>();
				
				while(rs.next()) {
					String fileOrgName = rs.getString(1);
					int idAttacheFile = rs.getInt(2);
					String fileSaveName = rs.getString(3);
					String fileDir = rs.getString(4);
					int fileSize = rs.getInt(5);
					String fileType = rs.getString(6);
					
					FileInfo info = new FileInfo("", idAttacheFile, fileOrgName, fileSaveName, fileDir, fileSize, fileType);
					imageList.add(info);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error(e.getLocalizedMessage());
			logger.error(e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		return imageList;
	}
	
	public FileInfo getImage(int idx) throws SQLException {
		String fileSql = "SELECT fileOrgName, idAttacheFile, fileSaveName, fileDir, fileSize, fileType "
				+ "FROM attachefile "
				+ "WHERE tableName='brand' AND tableKey=?";
		
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(fileSql);
		FileInfo info = null;
		
		try {
			pstmt.setInt(1, idx);
			ResultSet rs = pstmt.executeQuery();
			if (rs != null) {
				while(rs.next()) {
					String fileOrgName = rs.getString(1);
					int idAttacheFile = rs.getInt(2);
					String fileSaveName = rs.getString(3);
					String fileDir = rs.getString(4);
					int fileSize = rs.getInt(5);
					String fileType = rs.getString(6);
					
					info = new FileInfo("", idAttacheFile, fileOrgName, fileSaveName, fileDir, fileSize, fileType);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error(e.getLocalizedMessage());
			logger.error(e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		return info;
	}
	
	public FileInfo getImage2(int idx) throws SQLException {
		String fileSql = "SELECT fileOrgName, idAttacheFile, fileSaveName, fileDir, fileSize, fileType "
				+ "FROM attachefile "
				+ "WHERE tableName='brandbanner' AND tableKey=?";
		
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(fileSql);
		FileInfo info = null;
		
		try {
			pstmt.setInt(1, idx);
			ResultSet rs = pstmt.executeQuery();
			if (rs != null) {
				while(rs.next()) {
					String fileOrgName = rs.getString(1);
					int idAttacheFile = rs.getInt(2);
					String fileSaveName = rs.getString(3);
					String fileDir = rs.getString(4);
					int fileSize = rs.getInt(5);
					String fileType = rs.getString(6);
					
					info = new FileInfo("", idAttacheFile, fileOrgName, fileSaveName, fileDir, fileSize, fileType);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error(e.getLocalizedMessage());
			logger.error(e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		return info;
	}
	
	public ArrayList<BrandBannerCMD> getBrandBanner(int idx) throws SQLException {
		String sql = "SELECT idBrandBanner, bannerLinkType, bannerLinkCode, bannerImg "
				+ "FROM brandbanner "
				+ "WHERE isDisplay=1 AND idBrand=? "
				+ "ORDER BY sortKey, create_date DESC";
			
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		
		ArrayList<BrandBannerCMD> bannerList = null;
		
		try {
			pstmt.setInt(1, idx);
			ResultSet rs = pstmt.executeQuery();
			if (rs != null) {
				bannerList = new ArrayList<BrandBannerCMD>();
				
				while(rs.next()) {
					int idBrandBanner = rs.getInt(1);
					int bannerLinkType = rs.getInt(2);
					String bannerLinkCode = rs.getString(3);
					int bannerImg = rs.getInt(4);
					
					FileInfo image = this.getImage2(idBrandBanner);
					BrandBannerCMD item = new BrandBannerCMD(idBrandBanner, bannerLinkType, bannerLinkCode, bannerImg, image);
					bannerList.add(item);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error(e.getLocalizedMessage());
			logger.error(e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		return bannerList;
	}

}
